CREATE DATABASE XOMTRO
GO
USE XOMTRO
GO
CREATE TABLE Account_Type
(
	Id INT PRIMARY KEY IDENTITY(1,1) NOT NULL,
	Account_Type_Name NVARCHAR(1000),
)
CREATE TABLE Account
(
	Id INT PRIMARY KEY IDENTITY(1,1) NOT NULL,
	Account_Username NVARCHAR(100),
	Account_Password NVARCHAR(100),
	Account_Name NVARCHAR(100),
	Account_Address NVARCHAR(1000),
	Account_Phone NVARCHAR(100),
	Account_Email NVARCHAR(100),
	Account_Facebook NVARCHAR(100),
	Account_Created_Date DATE,
	Account_About NVARCHAR(1000),
	Account_Type_Id INT,
	Room_Id INT,
	Account_Info_Permission INT,
	Account_Status INT,
)

CREATE TABLE Room_Type
(
	Id INT PRIMARY KEY IDENTITY(1,1) NOT NULL,
	Room_Type_Name NVARCHAR(100),
)

CREATE TABLE Room_List
(
	Id INT PRIMARY KEY IDENTITY(1,1) NOT NULL,
	Room_List_Name NVARCHAR(100),
	Room_List_Description NVARCHAR(1000),
	Room_List_Created_Date DATE,
	Account_Id INT,
)

CREATE TABLE Room
(
	Id INT PRIMARY KEY IDENTITY(1,1) NOT NULL,
	Room_Name NVARCHAR(100),
	Room_Price INT, -- or FLOAT
	Room_Quantity_Guest INT,
	Room_Type_Id INT,
	Room_Address_Detail NVARCHAR(1000),
	Area_Id INT,
	Room_Longtitude FLOAT,
	Room_Latitude FLOAT,
	Room_Description NVARCHAR(1000),
	Room_List_Id INT,
	Room_Created_Date DATE,
	Room_Status NVARCHAR(1000),
	Liked_Count INT,
	Rating INT,
	Viewed_Count INT,
	Amentity XML,
	
)

CREATE TABLE Review_Room
(
	Id INT PRIMARY KEY IDENTITY(1,1) NOT NULL,
	Review_Content NVARCHAR(1000),
	Account_Id INT,
	Room_Id INT,
	Review_Date DATE,
)

CREATE TABLE Rating_Room
(
	Id INT PRIMARY KEY IDENTITY(1,1) NOT NULL,
	Room_Id INT,
	Account_Id INT,
)

CREATE TABLE Liked_Room
(
	Id INT PRIMARY KEY IDENTITY(1,1) NOT NULL,
	Room_Id INT,
	Account_Id INT,
)

CREATE TABLE City
(
	Id INT PRIMARY KEY IDENTITY(1,1) NOT NULL,
	City_Name NVARCHAR(100),
	City_Description NVARCHAR(MAX),
	City_Location XML,
)

CREATE TABLE District
(
	Id INT PRIMARY KEY IDENTITY(1,1) NOT NULL,
	District_Name NVARCHAR(100),
	District_Description NVARCHAR(MAX),
	City_Id INT,
	District_Location XML,
)

CREATE TABLE Area
(
	Id INT PRIMARY KEY IDENTITY(1,1) NOT NULL,
	Area_Name NVARCHAR(100),
	District_Id INT,
	Area_Description NVARCHAR(MAX),
	Area_Location XML,
)

CREATE TABLE Amentity
(
	Id INT PRIMARY KEY IDENTITY(1,1) NOT NULL,
	Amentity_Name NVARCHAR(100),
)

CREATE TABLE Utility_Type
(
	Id INT PRIMARY KEY IDENTITY(1,1) NOT NULL,
	Utility_Name NVARCHAR(100),
)

CREATE TABLE Utility_Place
(
	Id INT PRIMARY KEY IDENTITY(1,1) NOT NULL,
	Utility_Place_Name NVARCHAR(100),
	Utility_Place_Description NVARCHAR(1000),
	Utility_Type_Id INT,
	Utility_Latitude FLOAT,
	Utility_Longtitude FLOAT,
	Area_Id INT,
)

CREATE TABLE Status_On_Page
(
	Id INT PRIMARY KEY IDENTITY(1,1) NOT NULL,
	Status_Liked_Quantity INT,
	Status_Content NVARCHAR(1000),
	Status_Created_Date DATE,
	Status_Permission INT, -- limit permission in status
	Account_Id INT, --Host Id
)

CREATE TABLE Status_Comment
(
	Id INT PRIMARY KEY IDENTITY(1,1) NOT NULL,
	Account_Id INT, -- person commented
	Status_Id INT,
	Comment_Created_Date DATE,
)

CREATE TABLE Room_Comment
(
	Id INT PRIMARY KEY IDENTITY(1,1) NOT NULL,
	Account_Id INT, -- person commented
	Room_Id INT,
	Comment_Created_Date DATE,
)

CREATE TABLE Follow_Page
(
	Id INT PRIMARY KEY IDENTITY(1,1) NOT NULL,
	Own_Page INT,
	Member_Page INT,
)

CREATE TABLE Image_Data
(
	Id INT PRIMARY KEY IDENTITY(1,1) NOT NULL,
	Target_Id INT,
	Target_Type INT,
	Path_Image VARCHAR(100),
) 

ALTER TABLE Room ADD CONSTRAINT fk_Room_with_Room_Type FOREIGN KEY (Room_Type_Id) REFERENCES Room_Type(Id)
ALTER TABLE Room ADD CONSTRAINT fk_Room_with_Room_List FOREIGN KEY (Room_List_Id) REFERENCES Room_List(Id)
ALTER TABLE Room ADD CONSTRAINT fk_Room_with_Area FOREIGN KEY (Area_Id) REFERENCES Area(Id)
ALTER TABLE Room_List ADD CONSTRAINT fk_Room_List_with_Account FOREIGN KEY (Account_Id) REFERENCES Account(Id)
ALTER TABLE Rating_Room ADD CONSTRAINT fk_Rating_Room_with_Room FOREIGN KEY (Room_Id) REFERENCES Room(Id)
ALTER TABLE Rating_Room ADD CONSTRAINT fk_Rating_Room_with_Account FOREIGN KEY (Account_Id) REFERENCES Account(Id)
ALTER TABLE Liked_Room ADD CONSTRAINT fk_Liked_Room_with_Room FOREIGN KEY (Room_Id) REFERENCES Room(Id)
ALTER TABLE Liked_Room ADD CONSTRAINT fk_Liked_Room_with_Account FOREIGN KEY (Account_Id) REFERENCES Account(Id)
ALTER TABLE Review_Room ADD CONSTRAINT fk_Riew_Room_with_Room FOREIGN KEY (Room_Id) REFERENCES Room(Id)
ALTER TABLE Review_Room ADD CONSTRAINT fk_Riew_Room_with_Account FOREIGN KEY (Account_Id) REFERENCES Account(Id)
ALTER TABLE Account ADD CONSTRAINT fk_Account_with_Account_Type FOREIGN KEY (Account_Type_Id) REFERENCES Account_Type(Id)
ALTER TABLE Follow_Page ADD CONSTRAINT fk_Follow_Page_with_Account_1 FOREIGN KEY (Own_Page) REFERENCES Account(Id)
ALTER TABLE Follow_Page ADD CONSTRAINT fk_Follow_Page_with_Account_2 FOREIGN KEY (Member_Page) REFERENCES Account(Id)
ALTER TABLE Status_On_Page ADD CONSTRAINT fk_Status_On_Page_with_Account FOREIGN KEY (Account_Id) REFERENCES Account(Id)
ALTER TABLE Status_Comment ADD CONSTRAINT fk_Status_Comment_with_Account FOREIGN KEY (Account_Id) REFERENCES Account(Id)
ALTER TABLE Status_Comment ADD CONSTRAINT fk_Status_Comment_with_Status_On_Page FOREIGN KEY (Status_Id) REFERENCES Status_On_Page(Id)
ALTER TABLE District ADD CONSTRAINT fk_District_with_City FOREIGN KEY (City_Id) REFERENCES City(Id)
ALTER TABLE Area ADD CONSTRAINT fk_Area_with_District FOREIGN KEY (District_Id) REFERENCES District(Id)
ALTER TABLE Room_Comment ADD CONSTRAINT fk_Room_Comment_with_Account FOREIGN KEY (Account_Id) REFERENCES Account(Id)
ALTER TABLE Room_Comment ADD CONSTRAINT fk_Room_Comment_with_Room FOREIGN KEY (Room_Id) REFERENCES Room(Id)
ALTER TABLE Utility_Place ADD CONSTRAINT fk_Utility_Place_with_Utility_Type FOREIGN KEY (Utility_Type_Id) REFERENCES Utility_Type(Id)
ALTER TABLE Utility_Place ADD CONSTRAINT fk_Utility_Place_with_Area FOREIGN KEY (Area_Id) REFERENCES Area(Id)